import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.function.Function;

import cn.hutool.core.io.file.FileReader;
import cn.hutool.core.io.file.FileWriter;
import com.alibaba.fastjson2.JSON;
import com.wdh.dbutil.bean.DynamicFieldCfgBean;
import com.wdh.dbutil.bean.SelectCfgBean;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

public class ExportTableMetadataToExcel {

    @Test
    public void outputTableMetadataToExcel() {
        String url = "jdbc:mysql://localhost:3306/zdyn";
        String user = "root";
        String password = "root@123456";
        //todo 修改表名
        String tableName = "tb_shoushu_jieru";
        String tableDesc = "介入手术";


        String excelFilePath = "d:/字段解释/手术/" + tableDesc + ".xlsx";

        try (Connection connection = DriverManager.getConnection(url, user, password);
             FileOutputStream outputStream = new FileOutputStream(excelFilePath)) {

            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);

            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Table Metadata");

            // Create header row
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("原始字段");
            headerRow.createCell(1).setCellValue("程序字段");
            headerRow.createCell(2).setCellValue("数据类型");
            headerRow.createCell(3).setCellValue("备注");

            int rowNum = 1;
            while (columns.next()) {
                Row row = sheet.createRow(rowNum++);
                String columnName = columns.getString("COLUMN_NAME");
                row.createCell(0).setCellValue(columnName);
                row.createCell(1).setCellValue(transStr(columnName));
                //row.createCell(0).setCellValue(transStr(columnName));
                row.createCell(2).setCellValue(columns.getString("TYPE_NAME"));
                row.createCell(3).setCellValue(columns.getString("REMARKS"));
            }

            // Auto-size columns
            for (int i = 0; i < 3; i++) {
                sheet.autoSizeColumn(i);
            }

            workbook.write(outputStream);
            System.out.println("Table metadata exported to " + excelFilePath);

        } catch (SQLException | java.io.IOException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void outputTableMetadataToText() {
        String url = "jdbc:mysql://localhost:3306/zdyn";
        String user = "root";
        String password = "root@123456";
        //todo 修改表名
        String tableName = "tb_jiancha_shcta";
        String tableDesc = "术后CTA";


        String filePath = "d:/字段解释/" + tableDesc + "-字段解释.txt";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet columns = metaData.getColumns(null, null, tableName, null);


            List<String> lines = new ArrayList<>();
            lines.add("原始字段###程序字段###数据类型###备注");
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String typeName = columns.getString("TYPE_NAME");
                String columnName2 = transStr(columnName);
                String remarks = columns.getString("REMARKS");
                lines.add(columnName + "###" + tableName + "-" + columnName2 + "###" + typeName + "###" + remarks);
            }
            FileWriter writer = new FileWriter(filePath);
            writer.writeLines(lines);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * txt文本格式
     * 原始字段###程序字段###数据类型###备注###text###include
     * 原始字段###程序字段###数据类型###备注###text###compare
     * 原始字段###程序字段###数据类型###备注###text###compare
     * 原始字段###程序字段###数据类型###备注###text###compare
     * 原始字段###程序字段###数据类型###备注###date###range
     * 原始字段###程序字段###数据类型###备注###select###aaa,bbb,ccc
     * */
    @Test
    public void parseText2FieldCfg(){
        //todo
        String belong = "";
        String filePath = "d:/字段解释/aaa.text";

        Function<String, String> transDataType = (str) -> {
            if (str.equalsIgnoreCase("int")){
                return "integer";
            }
            if (str.equalsIgnoreCase("bigint")){
                return "long";
            }
            if (str.equalsIgnoreCase("decimal")){
                return "decimal";
            }
            if (str.equalsIgnoreCase("varchar")){
                return "string";
            }
            if (str.equalsIgnoreCase("datetime")){
                return "string";
            }
            return "";
        };

        List<DynamicFieldCfgBean> cfgBeans = new ArrayList<>();
        FileReader reader = new FileReader(filePath);
        List<String> lines = reader.readLines();
        String delimiter = "###";
        lines.forEach(line -> {
            String[] arr = line.split(delimiter);
            String colName = arr[0];
            String fieldName = arr[1];
            String dataType = arr[2];
            String remark = arr[3];
            String selectType = arr[4];
            String extInfo = arr[5];
            String javaType = transDataType.apply(dataType);
            String chooseItems = "";

            if (extInfo.equals("include")){
                chooseItems = createIncludeCfg();
            }else if (extInfo.equals("have")){
                chooseItems = createHaveCfg();
            }else if (extInfo.equals("compare")){
                chooseItems = createCompareCfg();
            }else if (extInfo.equals("good")){
                chooseItems = createGoodCfg();
            }else if (extInfo.indexOf(",") >= 0){
                chooseItems = createFromArray(extInfo.split(","));
            }

            DynamicFieldCfgBean cfg = new DynamicFieldCfgBean();
            cfg.setSelectName(fieldName);
            cfg.setShowName(remark);
            cfg.setValueType(javaType);
            cfg.setSelectType(selectType);
            cfg.setChooseItems(chooseItems);
            cfg.setBelong(belong);
            cfg.setYnDelete(0);
            cfgBeans.add(cfg);
        });

        // SQL 插入语句
        String sql = "INSERT INTO tb_dynamic_query_cfg (select_name, show_name, value_type,select_type, choose_items, belong, create_time, yn_delete) VALUES" +
                    " (?,?,?,?,?,?,now(),0)";

        // 要插入的数据
        int size = cfgBeans.size();
        Object[][] params = new Object[size][6];
        for (int i = 0; i < size; i++) {
            DynamicFieldCfgBean cfg = cfgBeans.get(i);
            params[i] = new Object[]{cfg.getSelectName(), cfg.getShowName(), cfg.getValueType(), cfg.getSelectType(), cfg.getChooseItems(), cfg.getBelong()};
        }

        String url = "jdbc:mysql://localhost:3306/zdyn";
        String user = "root";
        String password = "root@123456";
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            QueryRunner queryRunner = new QueryRunner();
            int[] updateCounts = queryRunner.batch(conn, sql, params);

            // 输出插入结果
            for (int count : updateCounts) {
                System.out.println("Rows updated: " + count);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void parseText2GenSql(){
        //todo
        String sourceTextFile = "D:\\字段解释\\术后CTA-字段解释.txt";

        List<String> excludeFields = Arrays.asList("id","create_time","update_time","create_user","update_user","is_delete","yn_delete","patient_id");
        String dateQueryFile = "D:\\字段解释\\dateQuery.txt";
      String textQueryFile = "D:\\字段解释\\textQuery.txt";
      FileReader reader = new FileReader(dateQueryFile);
      String dateQueryContent = reader.readString();
      reader = new FileReader(textQueryFile);
      String textQueryContent = reader.readString();


      reader = new FileReader(sourceTextFile);
      List<String> lines = reader.readLines();

    }

    public static String transStr(String str){
        if (StringUtils.isEmpty(str)){
            return str;
        }
        String[] arr = str.split("_");
        int len = arr.length;
        StringBuilder builder = new StringBuilder(arr[0]);
        for (int i = 1; i < len ; i++) {
            String s = arr[i];
            s = s.substring(0,1).toUpperCase() + s.substring(1);
            builder.append(s);
        }
        return builder.toString();
    }

    @Test
    public void genSql(){
        String colFile = "D:\\字段解释\\检验项目\\sourceCol.txt";
        String sqlFile = "D:\\字段解释\\检验项目\\sourceSql.txt";

        FileReader sqlReader = new FileReader(sqlFile);
        String content = sqlReader.readString();

        List<String> lines = new ArrayList<>();
        FileReader colReader = new FileReader(colFile);
        colReader.readLines().forEach(col -> {
            String replace = content.replace("colName", col);
            lines.add(replace);
        });

        FileWriter fileWriter = new FileWriter("D:\\字段解释\\检验项目\\destSql.txt");
        fileWriter.writeLines(lines);
    }

    @Test
    public void sdfsa(){
        System.out.println("=========");
        System.out.println(createWeatherCfg());
    }

    private String createWeatherCfg(){
        List<SelectCfgBean> list = Arrays.asList(new SelectCfgBean("=", "是"), new SelectCfgBean("=", "否"));
        return JSON.toJSONString(list);
    }

    private String createIncludeCfg(){
        List<SelectCfgBean> list = Arrays.asList(new SelectCfgBean("like", "包含"), new SelectCfgBean("not like", "不包含"));
        return JSON.toJSONString(list);
    }

    private String createHaveCfg(){
        List<SelectCfgBean> list = Arrays.asList(new SelectCfgBean("=", "有"), new SelectCfgBean("=", "无"));
        return JSON.toJSONString(list);
    }

    private String createGoodCfg(){
        List<SelectCfgBean> list = Arrays.asList(new SelectCfgBean("=", "好"), new SelectCfgBean("=", "不好"));
        return JSON.toJSONString(list);
    }

    private String createCompareCfg(){
        List<SelectCfgBean> list = Arrays.asList(
                new SelectCfgBean(">", "大于"),
                new SelectCfgBean(">=", "大于等于"),
                new SelectCfgBean("<", "小于"),
                new SelectCfgBean("<=", "小于等于"),
                new SelectCfgBean("=", "等于")
        );
        return JSON.toJSONString(list);
    }

    private String createFromArray(String[] arr){
        List<SelectCfgBean> list = new ArrayList<>();
        for (int i = 0; i < arr.length; i++) {
            list.add(new SelectCfgBean("=", arr[i]));
        }
        return JSON.toJSONString(list);
    }
}
